USE [toro.hrm]
GO

----Lowest, Low, Normal, High, Highest, Urgent
CREATE TABLE [dbo].[wpm_priority]
(
	priorityid [uniqueidentifier] primary key not null,
	priorityname [nvarchar](300) NULL,
	prioritycolor [varchar] NULL,
	photo [image] NULL,
	isusecolor [bit] NULL,
	isusephoto [bit] NULL,
	active [bit] NULL,
)
GO

---Draft , Created, In Progress, Cancelled, Completed, Verified
CREATE TABLE [dbo].[wpm_status]
(
	statusid [uniqueidentifier] primary key not null,
	statusname [nvarchar](300) NULL,
	statuscolor [varchar] NULL,
	photo [image] NULL,
	isusecolor [bit] NULL,
	isusephoto [bit] NULL,
	active [bit] NULL,
)
GO

CREATE TABLE [dbo].[wpm_project]
(
	projectid [uniqueidentifier] primary key not null,
	projectname [nvarchar](300) NULL,
	projectempcodemanager [varchar](100),-- foreign key
	projectempcodeleader [varchar](100), -- foreign key
	projectstarttime [datetime] NULL,
	projectfinishtime [datetime] NULL,
	projectestimatetimecost [datetime] NULL,
	projectactualtimecost [datetime] NULL,
	projectduedate [datetime] NULL,
	projectcomplete [float] NULL,
	projectdescription [nvarchar](400),
	projectattachmentpath [nvarchar](MAX) NULL, 
	projectactive [bit] NULL,
	
	FOREIGN KEY (projectempcodemanager) REFERENCES hrm_employee(employeecode),
	FOREIGN KEY (projectempcodeleader) REFERENCES hrm_employee(employeecode),
)
GO

CREATE TABLE [dbo].[wpm_milestone]
(
	milestoneid [uniqueidentifier] primary key not null,
	milestoneparentid [uniqueidentifier] NULL, -- foreign key
	milestonelevel int default 0,
	milestonename [nvarchar](300) NULL,
	milestonestarttime [datetime] NULL,
	milestonefinishtime [datetime] NULL,
	milestoneestimatetimecost [datetime] NULL,
	milestoneactualtimecost [datetime] NULL,
	milestoneduedate [datetime] NULL,
	milestonecomplete [float] NULL,
	milestonedescription [nvarchar](400),
	milestoneactive [bit] NULL,
	milestoneempcodemanager [varchar](100),-- foreign key
	milestoneempcodeleader [varchar](100), -- foreign key
	milestonepriorityid [uniqueidentifier],-- foreign key
	projectid [uniqueidentifier] NULL, -- foreign key

	FOREIGN KEY (milestoneparentid) REFERENCES wpm_milestone(milestoneid),
	FOREIGN KEY (milestoneempcodemanager) REFERENCES hrm_employee(employeecode),
	FOREIGN KEY (milestoneempcodeleader) REFERENCES hrm_employee(employeecode),
	FOREIGN KEY (milestonepriorityid) REFERENCES wpm_priority(priorityid),
	FOREIGN KEY (projectid) REFERENCES [wpm_project](projectid)
)
GO

CREATE TABLE [dbo].[wpm_milestone_attachment]
(
	attachmentid [uniqueidentifier] primary key not null,
	attachmentpath [nvarchar](MAX) NULL, 
	attachmentdescription [nvarchar](400),
	attachmentversion [varchar](100) default '1.0',
	milestoneid [uniqueidentifier] NULL, -- foreign key
	
	FOREIGN KEY (milestoneid) REFERENCES [wpm_milestone](milestoneid)
)

--groupcode [nvarchar](100) NULL,
CREATE TABLE [dbo].[wpm_task]
(
	taskid [uniqueidentifier] not null primary key,
	taskparentid [uniqueidentifier] NULL, -- foreign key
	tasklevel int default 0,
	taskname [nvarchar](200) null,
	taskdescription [nvarchar](400),
	taskstarttime [datetime] NULL,
	taskfinishtime [datetime] NULL,
	taskestimatetimecost [datetime] NULL,
	taskactualtimecost [datetime] NULL,
	taskduedate [datetime] NULL,
	taskcomplete [float] NULL,
	taskactive [bit] NULL,
	taskemployeecode [varchar](100) NULL, -- foreign key
	taskstatusid [uniqueidentifier] NULL,-- foreign key
	taskmilestoneid [uniqueidentifier],-- foreign key
	
	FOREIGN KEY (taskid) REFERENCES wpm_task(taskid),
	FOREIGN KEY (taskemployeecode) REFERENCES hrm_employee(employeecode),
	FOREIGN KEY (taskstatusid) REFERENCES wpm_status(statusid),	
	FOREIGN KEY (taskmilestoneid) REFERENCES [wpm_milestone](milestoneid)
)
GO

CREATE TABLE [dbo].[wpm_task_attachment]
(
	attachmentid [uniqueidentifier] primary key not null,
	attachmentpath [nvarchar](MAX) NULL, 
	attachmentdescription [nvarchar](400),
	attachmentversion [varchar](100) default '1.0',
	taskid [uniqueidentifier] NULL, -- foreign key
	
	FOREIGN KEY (taskid) REFERENCES wpm_task(taskid)
)



CREATE TABLE [dbo].[wpm_reminder]
(
	remindid [uniqueidentifier] not null primary key,
	remindsubject [nvarchar](100) NULL,
	remindmessagetext [nvarchar](160) NULL, -- approirate for the SMS message
	remindtaskid [uniqueidentifier] NULL, -- foreign key
	remindemployeecode [varchar](100) NULL, -- foreign key
	remindstarttime [datetime] NULL,
	remindendtime [datetime] NULL,
	isemailremind [bit] NULL,
	issmsremind [bit] NULL,
	isallremind [bit] NULL,
	
	FOREIGN KEY (remindtaskid) REFERENCES wpm_task(taskid),
	FOREIGN KEY (remindemployeecode) REFERENCES hrm_employee(employeecode)
)
GO

CREATE TABLE [dbo].[wpm_comment]
(
	commentid [uniqueidentifier] not null primary key,
	commenttaskid [uniqueidentifier] NULL,
	commentemployeecode [varchar](100) NULL,
	commentcommenttext [nvarchar](MAX),
	commentactive [bit],
	
	FOREIGN KEY (commenttaskid) REFERENCES wpm_task(taskid),
	FOREIGN KEY (commentemployeecode) REFERENCES hrm_employee(employeecode)
)
GO